/*******************************************************************************
* 1. 시 스 템 : 농협보험 이미지처리 시스템
* 2. 프로그램 : DBConnection.java
* 3. 개 요 : DBConnection을 Pool로 관리한다.
* 4. 작 성 자 : 손 영휘 
* 5. 작 성 일 : 2011. 09. 30
* 6. 소스버전 : 1.0
********************************************************************************/
package com.nhim.common;

//import java.net.*;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import com.nhim.pool.ConnectionPool;
import com.nhim.pool.DBConnection;
import com.nhim.utility.COMUtil;


public class NextJdgNo
{	
	//private static NextAskWorker m_oInstance;
	
	private NextJdgNo()
	{		
	}
	
	// 지역본부,심사센터 이외의 심사그룹 
	public static synchronized String getNextJdgNo(Connection oConn, String sJdgGroup, String sRecpRgnofcCode, 
			String sRecpRegnCode) throws Exception {
		int nRtn = 0;
		
		PreparedStatement pstmt = null;	
		ResultSet rs = null;
		
		StringBuffer sbQuery = new StringBuffer();
		StringBuffer sbUpdate = new StringBuffer();
		
		String sNextJdgNo = "";
		
		COMUtil.debug(">>> [getNextJdgNo] Start... <<<");
		
		try {
			
			sbQuery.append("\n	SELECT NVL(MIN(JDG_NO), '0') JDG_NO			");
			sbQuery.append("\n	FROM				");
			sbQuery.append("\n	(				");
			sbQuery.append("\n	    select a.JDG_NO,		");			// 심사자번호
			sbQuery.append("\n	        a.JDG_NM,		");				// 심사자명
			sbQuery.append("\n	        a.JDGMT_GRP_CODE,	");			// 그룹코드
			//sbQuery.append("\n	        a.JDGMT_GRP_NM,		");			// 심사그룹명
			sbQuery.append("\n	        a.CHRG_RGNOFC_CODE,	");			// 담당지역본부코드
			//sbQuery.append("\n	        a.CHRG_RGNOFC_NM,	");			// 담당지역본부명
			//sbQuery.append("\n	        a.CHRG_REGN_CODE,       ");		// 담당권역코드
			//sbQuery.append("\n	        a.CHRG_REGN_NM,         ");		// 담당권역명
			//sbQuery.append("\n	        a.AUTO_ASGN_OBJ_YN,     ");		// 자동배정대상여부
			sbQuery.append("\n	        a.AUTO_ASGN_LIM_CNT,    ");		// 자동배정한도건수
			//sbQuery.append("\n	        a.VOC_STRT_DATE,        ");		// 부재시작일자
			//sbQuery.append("\n	        a.VOC_END_DATE,         ");		// 부재종료일자
			sbQuery.append("\n			a.WORK_ALT_YN,			");		// 작업할당 여부
			//sbQuery.append("\n	        b.ONLN_CODE,            ");		// 심사자온라인코드
			//sbQuery.append("\n	        c.OFFICE_CODE,          ");		// 심사자사무소코드
			sbQuery.append("\n	        (SELECT COUNT(*) 	");			// 현재 심사진행건수
			sbQuery.append("\n	            FROM LI_CLM_110	");
			sbQuery.append("\n	            WHERE JDG_NO = a.JDG_NO				");
			sbQuery.append("\n	                and JDGMT_STAT in ('1', '2')			");
			sbQuery.append("\n	                and (DEL_YN <> 'Y' or DEL_YN is null)	");
			sbQuery.append("\n	        ) JDGMT_CNT						");
			sbQuery.append("\n	    from LI_IMN_010 a			");
			sbQuery.append("\n	    where 1=1					");
			//sbQuery.append("\n	        and b.ONLN_CODE = c.ONLN_CODE				");
			sbQuery.append("\n	        and (DEL_YN <> 'Y' or DEL_YN is null)			");
			sbQuery.append("\n	        and AUTO_ASGN_OBJ_YN = 'Y'				");
			sbQuery.append("\n	        and (VALD_STRT_DATE is NULL or (VALD_STRT_DATE <= TO_CHAR(SYSDATE, 'YYYYMMDD')))	");
			sbQuery.append("\n	        and (VALD_END_DATE is NULL or (VALD_END_DATE >= TO_CHAR(SYSDATE, 'YYYYMMDD')))		");
			sbQuery.append("\n	        and ( NVL(VOC_STRT_DATE,'99991231') > TO_CHAR(SYSDATE, 'YYYYMMDD') or NVL(VOC_END_DATE,'0') < TO_CHAR(SYSDATE, 'YYYYMMDD'))	");
			//sbQuery.append("\n	        and (VOC_END_DATE is NULL or (VOC_END_DATE < TO_CHAR(SYSDATE, 'YYYYMMDD')))		");
			sbQuery.append("\n	        and JDGMT_AUTHY_YN = 'Y'								");
			sbQuery.append("\n	) X												");
			sbQuery.append("\n	WHERE X.JDGMT_CNT < AUTO_ASGN_LIM_CNT			");
			sbQuery.append("\n			AND X.JDGMT_GRP_CODE = ?					");
			sbQuery.append("\n		AND X.WORK_ALT_YN = 'N'					");
							
			pstmt = oConn.prepareStatement(sbQuery.toString());
			pstmt.clearParameters();
			pstmt.setString(1, sJdgGroup);
			rs = pstmt.executeQuery();
			
			if(rs.next()){
				sNextJdgNo = rs.getString("JDG_NO").trim();
				
				if("0".equals(sNextJdgNo)) {	// 모두 할당된 상태
				
					sNextJdgNo = resetNextJdgNo(oConn, sJdgGroup, sRecpRgnofcCode, sRecpRegnCode);
				}
				else {
					if(rs != null) { rs.close(); rs = null; }
					if(pstmt != null) { pstmt.close(); pstmt = null; }

					sbUpdate.append("\n	UPDATE LI_IMN_010 ");
					sbUpdate.append("\n  	SET WORK_ALT_YN = 'Y'	");				
					sbUpdate.append("\n  WHERE JDG_NO = ?			");
					
					pstmt = oConn.prepareStatement(sbUpdate.toString());
					pstmt.clearParameters();
					pstmt.setString(1, sNextJdgNo);
					nRtn = pstmt.executeUpdate();
					
				}
				
				COMUtil.debug("getNextJdgNo="+sNextJdgNo);
			}
			
		}
		catch(Exception e) {
			COMUtil.debug("[sNextJdgNo] Exception : " + e.toString());
			throw new Exception("[sNextJdgNo] Exception:"+e.toString());
		}
		finally {
			if(rs != null) { rs.close(); rs=null;}
			if(pstmt != null){ pstmt.close(); pstmt=null;}
		}
		
		COMUtil.debug(">>> [getNextJdgNo] End... <<<");
		
		return sNextJdgNo;
	}
	
	// 지역본부 심사그룹
	// 지역본부 외 심사센터 추가
	public static synchronized String getNextJdgNo2(Connection oConn, String sJdgGroup, String sRecpRgnofcCode, 
			String sRecpRegnCode) throws Exception {
		int nRtn = 0;
		
		PreparedStatement pstmt = null;	
		ResultSet rs = null;
		
		StringBuffer sbQuery = new StringBuffer();
		StringBuffer sbQuery2 = new StringBuffer();
		StringBuffer sbUpdate = new StringBuffer();
		
		String sNextJdgNo = "";
		int nExist = 0;
		int nCnt = 0;
		
		COMUtil.debug(">>> [getNextJdgNo2] Start... <<<");
		COMUtil.debug(">>> sRecpRgnofcCode = " + sRecpRgnofcCode );
		COMUtil.debug(">>> sRecpRegnCode = " + sRecpRegnCode );
		
		try {
			
			sbQuery.append("\n	SELECT X.*		");
			sbQuery.append("\n	FROM				");
			sbQuery.append("\n	(				");
			sbQuery.append("\n	    select a.JDG_NO,		");			// 심사자번호
			sbQuery.append("\n	        a.JDGMT_GRP_CODE,	");			// 그룹코드
			sbQuery.append("\n	        a.AUTO_ASGN_LIM_CNT,    ");		// 자동배정한도건수
			sbQuery.append("\n	        a.CHRG_RGNOFC_CODE,	");			// 담당지역본부코드
			sbQuery.append("\n			a.WORK_ALT_YN,			");		// 작업할당 여부
			sbQuery.append("\n	        (SELECT COUNT(*) 	");			// 현재 심사진행건수 --> 당일배정건수로 변경
			sbQuery.append("\n	            FROM LI_CLM_110	");
			sbQuery.append("\n	            WHERE JDG_NO = a.JDG_NO				");
			//sbQuery.append("\n	                and JDGMT_STAT in ('1', '2')			");				//20111208 deleted by Badboy
			sbQuery.append("\n	                and ASGN_DATE = TO_CHAR(SYSDATE, 'YYYYMMDD') ");			//20111208 added by Badboy
			sbQuery.append("\n	                and (DEL_YN <> 'Y' or DEL_YN is null)	");
			sbQuery.append("\n	        ) JDGMT_CNT						");
			sbQuery.append("\n	    from LI_IMN_010 a					");
			sbQuery.append("\n	    where 1=1					");
			sbQuery.append("\n	        and (DEL_YN <> 'Y' or DEL_YN is null)			");
			sbQuery.append("\n	        and AUTO_ASGN_OBJ_YN = 'Y'				");
			sbQuery.append("\n	        and (VALD_STRT_DATE is NULL or (VALD_STRT_DATE <= TO_CHAR(SYSDATE, 'YYYYMMDD')))	");
			sbQuery.append("\n	        and (VALD_END_DATE is NULL or (VALD_END_DATE >= TO_CHAR(SYSDATE, 'YYYYMMDD')))		");
			sbQuery.append("\n	        and (VOC_STRT_DATE is NULL or (VOC_STRT_DATE > TO_CHAR(SYSDATE, 'YYYYMMDD')))		");
			sbQuery.append("\n	        and (VOC_END_DATE is NULL or (VOC_END_DATE < TO_CHAR(SYSDATE, 'YYYYMMDD')))		");
			sbQuery.append("\n	        and JDGMT_AUTHY_YN = 'Y'								");
			sbQuery.append("\n	) X												");
			sbQuery.append("\n	WHERE X.JDGMT_CNT < AUTO_ASGN_LIM_CNT			");
			//sbQuery.append("\n			AND X.JDGMT_GRP_CODE in ('10', '20')		");
			sbQuery.append("\n			AND X.JDGMT_GRP_CODE in ('20')		");			//20111208 modified by Badboy : 3월이전까지 심사센터는 배분 안함
			sbQuery.append("\n			AND X.CHRG_RGNOFC_CODE = ?					");
			sbQuery.append("\n	ORDER BY X.JDG_NO ASC								");
							
			pstmt = oConn.prepareStatement(sbQuery.toString());
			pstmt.clearParameters();
			pstmt.setString(1, sRecpRgnofcCode);
			rs = pstmt.executeQuery();
			
			while(rs.next()){
				nCnt++;
				String sWorkAltYn = rs.getString("WORK_ALT_YN");
				
				if("N".equals(sWorkAltYn)) {
					sNextJdgNo = rs.getString("JDG_NO");
					nExist = 1;
					break;
				}
			}
			
			if(rs != null) { rs.close(); rs = null; }
			if(pstmt != null) { pstmt.close(); pstmt = null; }
			
			COMUtil.debug(">>> 1. sNextJdgNo=" + sNextJdgNo + "  <<<");

			if(nCnt == 0) {	// 권역내의 다른 지역본부로...
				// 권역내 다른 지역본부 검색
				sNextJdgNo = getNextJdgNoFromOther(oConn, sJdgGroup, sRecpRgnofcCode, sRecpRegnCode);
			}
			else {			
				if(nExist == 0) {	// 모두 할당된 상태
					sNextJdgNo = resetNextJdgNo(oConn, sJdgGroup, sRecpRgnofcCode, sRecpRegnCode);
				}
				else {	
					sbUpdate.append("\n	UPDATE LI_IMN_010 ");
					sbUpdate.append("\n  	SET WORK_ALT_YN = 'Y'	");				
					sbUpdate.append("\n  WHERE JDG_NO = ?			");
					
					pstmt = oConn.prepareStatement(sbUpdate.toString());
					pstmt.clearParameters();
					pstmt.setString(1, sNextJdgNo);
					nRtn = pstmt.executeUpdate();
				}
			}
			
		}
		catch(Exception e) {
			COMUtil.debug("[getNextJdgNo2] Exception : " + e.toString());
			throw new Exception("[getNextJdgNo2] Exception:"+e.toString());
		}
		finally {
			if(rs != null) { rs.close(); rs=null;}
			if(pstmt != null){ pstmt.close(); pstmt=null;}
		}
		
		COMUtil.debug(">>> [getNextJdgNo2] End... <<<");
		
		return sNextJdgNo;
	}

	public static String getNextJdgNoFromOther(Connection oConn, String sJdgGroup, String sRecpRgnofcCode, 
			String sRecpRegnCode) throws Exception {
		
		String sNextJdgNo = "";
		int nExist = 0;
		int nCnt = 0;
		int nRtn = 0;
		
		COMUtil.debug(">>> [getNextJdgNoFromOther] Start... <<<");
		PreparedStatement pstmt = null;	
		ResultSet rs = null;
		
		StringBuffer sbQuery = new StringBuffer();
		StringBuffer sbUpdate = new StringBuffer();
		
		try {
			sbQuery.append("\n	SELECT X.*		");
			sbQuery.append("\n	FROM				");
			sbQuery.append("\n	(				");
			sbQuery.append("\n	    select a.JDG_NO,		");			// 심사자번호
			sbQuery.append("\n	        a.JDGMT_GRP_CODE,	");			// 그룹코드
			sbQuery.append("\n	        a.AUTO_ASGN_LIM_CNT,    ");		// 자동배정한도건수
			sbQuery.append("\n	        a.CHRG_RGNOFC_CODE,	");			// 담당지역본부코드
			sbQuery.append("\n			a.WORK_ALT_YN,			");		// 작업할당 여부
			sbQuery.append("\n			a.CHRG_REGN_CODE,		");		// 담당권역코드			
			sbQuery.append("\n	        (SELECT COUNT(*) 	");			// 현재 심사진행건수 --> 당일배정건수로 변경
			sbQuery.append("\n	            FROM LI_CLM_110	");
			sbQuery.append("\n	            WHERE JDG_NO = a.JDG_NO				");
			sbQuery.append("\n	                and ASGN_DATE = TO_CHAR(SYSDATE, 'YYYYMMDD') ");			//20111208 added by Badboy
			sbQuery.append("\n	                and (DEL_YN <> 'Y' or DEL_YN is null)	");
			sbQuery.append("\n	        ) JDGMT_CNT						");
			sbQuery.append("\n	    from LI_IMN_010 a					");
			sbQuery.append("\n	    where 1=1					");
			sbQuery.append("\n	        and (DEL_YN <> 'Y' or DEL_YN is null)			");
			sbQuery.append("\n	        and AUTO_ASGN_OBJ_YN = 'Y'				");
			sbQuery.append("\n	        and (VALD_STRT_DATE is NULL or (VALD_STRT_DATE <= TO_CHAR(SYSDATE, 'YYYYMMDD')))	");
			sbQuery.append("\n	        and (VALD_END_DATE is NULL or (VALD_END_DATE >= TO_CHAR(SYSDATE, 'YYYYMMDD')))		");
			sbQuery.append("\n	        and (VOC_STRT_DATE is NULL or (VOC_STRT_DATE > TO_CHAR(SYSDATE, 'YYYYMMDD')))		");
			sbQuery.append("\n	        and (VOC_END_DATE is NULL or (VOC_END_DATE < TO_CHAR(SYSDATE, 'YYYYMMDD')))		");
			sbQuery.append("\n	        and JDGMT_AUTHY_YN = 'Y'								");
			sbQuery.append("\n	        and JDGMT_GRP_CODE = '20'								");
			sbQuery.append("\n	        and CHRG_REGN_CODE = ?									");
			sbQuery.append("\n	        and CHRG_RGNOFC_CODE <> ?								");			
			sbQuery.append("\n	) X												");
			sbQuery.append("\n	WHERE X.JDGMT_CNT < AUTO_ASGN_LIM_CNT			");			
			sbQuery.append("\n	ORDER BY X.CHRG_RGNOFC_CODE, X.JDG_NO ASC		");
							
			pstmt = oConn.prepareStatement(sbQuery.toString());
			pstmt.clearParameters();
			pstmt.setString(1, sRecpRegnCode);
			pstmt.setString(2, sRecpRgnofcCode);
			rs = pstmt.executeQuery();
			
			while(rs.next()){
				COMUtil.debug(">>> [getNextJdgNoFromOther] exist jdgno <<<");
				nCnt++;
				String sWorkAltYn = rs.getString("WORK_ALT_YN");
				
				if("N".equals(sWorkAltYn)) {
					sNextJdgNo = rs.getString("JDG_NO");
					nExist = 1;
					break;
				}
			}
			
			if(rs != null) { rs.close(); rs = null; }
			if(pstmt != null) { pstmt.close(); pstmt = null; }
			
			COMUtil.debug(">>> sNextJdgNo From Other =" + sNextJdgNo + "  <<<");

			if(nCnt == 0) {	// 배정 대상 심사자 없음...
				COMUtil.debug(">>> [getNextJdgNoFromOther] : No JdgNo  <<<");
			}
			else {			
				if(nExist == 0) {	// 모두 할당된 상태
					sNextJdgNo = resetNextJdgNo2(oConn, sJdgGroup, sRecpRgnofcCode, sRecpRegnCode);
				}
				else {	
					sbUpdate.append("\n	UPDATE LI_IMN_010 ");
					sbUpdate.append("\n  	SET WORK_ALT_YN = 'Y'	");				
					sbUpdate.append("\n  WHERE JDG_NO = ?			");
					
					pstmt = oConn.prepareStatement(sbUpdate.toString());
					pstmt.clearParameters();
					pstmt.setString(1, sNextJdgNo);
					nRtn = pstmt.executeUpdate();
				}
			}
			
		} catch(Exception e) {
			COMUtil.debug("[getNextJdgNoFromOther] Exception : " + e.toString());
			throw new Exception("[getNextJdgNoFromOther] Exception:"+e.toString());
		}
		finally {
			if(rs != null) { rs.close(); rs=null;}
			if(pstmt != null){ pstmt.close(); pstmt=null;}
		}
		
		return sNextJdgNo;		
	}
	
	public static String resetNextJdgNo(Connection oConn, String sJdgGroup, String sRecpRgnofcCode, 
			String sRecpRegnCode) throws Exception {
		
		COMUtil.debug(">>> [resetNextJdgNo] Start... <<<");
		COMUtil.debug(">>> sRecpRgnofcCode = "+ sRecpRgnofcCode);
		String sNextJdgNo = "";
		
		PreparedStatement pstmt = null;	
		ResultSet rs = null;
		
		StringBuffer sbQuery = new StringBuffer();
		StringBuffer sbQuery2 = new StringBuffer();
		String sQuery = "";
		
		try {
			sbQuery.append("\n	SELECT NVL(MIN(JDG_NO), '0') JDG_NO			");
			sbQuery.append("\n	FROM				");
			sbQuery.append("\n	(				");
			sbQuery.append("\n	    select a.JDG_NO,		");			// 심사자번호
			sbQuery.append("\n	        a.JDG_NM,		");				// 심사자명
			sbQuery.append("\n	        a.JDGMT_GRP_CODE,	");			// 그룹코드
			sbQuery.append("\n	        a.AUTO_ASGN_LIM_CNT,    ");		// 자동배정한도건수
			sbQuery.append("\n			a.WORK_ALT_YN,			");		// 작업할당 여부
			sbQuery.append("\n			a.CHRG_RGNOFC_CODE,			");	// 담당지역본부
			sbQuery.append("\n	        (SELECT COUNT(*) 	");			// 현재 심사진행건수 --> 당일배정건수로 변경
			sbQuery.append("\n	            FROM LI_CLM_110	");
			sbQuery.append("\n	            WHERE JDG_NO = a.JDG_NO				");
			sbQuery.append("\n	                and ASGN_DATE = TO_CHAR(SYSDATE, 'YYYYMMDD') ");			//20111208 added by Badboy
			sbQuery.append("\n	                and (DEL_YN <> 'Y' or DEL_YN IS NULL )	");
			sbQuery.append("\n	        ) JDGMT_CNT						");
			sbQuery.append("\n	    from LI_IMN_010 a  			");
			sbQuery.append("\n	    where 1=1						");
			sbQuery.append("\n	        and (DEL_YN <> 'Y' or DEL_YN is null)			");
			sbQuery.append("\n	        and AUTO_ASGN_OBJ_YN = 'Y'				");
			sbQuery.append("\n	        and (VALD_STRT_DATE is NULL or (VALD_STRT_DATE <= TO_CHAR(SYSDATE, 'YYYYMMDD')))	");
			sbQuery.append("\n	        and (VALD_END_DATE is NULL or (VALD_END_DATE >= TO_CHAR(SYSDATE, 'YYYYMMDD')))		");
			sbQuery.append("\n	        and (VOC_STRT_DATE is NULL or (VOC_STRT_DATE > TO_CHAR(SYSDATE, 'YYYYMMDD')))		");
			sbQuery.append("\n	        and (VOC_END_DATE is NULL or (VOC_END_DATE < TO_CHAR(SYSDATE, 'YYYYMMDD')))		");
			sbQuery.append("\n	        and JDGMT_AUTHY_YN = 'Y'								");
			sbQuery.append("\n	) X												");
			sbQuery.append("\n	WHERE X.JDGMT_CNT < AUTO_ASGN_LIM_CNT			");
			sbQuery.append("\n			AND X.JDGMT_GRP_CODE = ?					");
			
			if("20".equals(sJdgGroup)) 
				sbQuery.append("\n			AND X.CHRG_RGNOFC_CODE = ?					");

			pstmt = oConn.prepareStatement(sbQuery.toString());
			pstmt.clearParameters();
			pstmt.setString(1, sJdgGroup);
			
			if("20".equals(sJdgGroup))
				pstmt.setString(2, sRecpRgnofcCode);
			
			rs = pstmt.executeQuery();
			
			if(rs.next())
				sNextJdgNo = rs.getString("JDG_NO");
			
			rs.close(); rs = null;
			pstmt.close(); pstmt = null;

			if(!"0".equals(sNextJdgNo)) {
				sbQuery2.append("\n	UPDATE LI_IMN_010			");
				sbQuery2.append("\n	SET	WORK_ALT_YN = 'N'		");
				sbQuery2.append("\n	WHERE JDG_NO IN ( 			");
				sbQuery2.append("\n		SELECT X.JDG_NO			");
				sbQuery2.append("\n		FROM ( 					");
				sbQuery2.append("\n	    	select a.JDG_NO,		");			// 심사자번호
				sbQuery2.append("\n	        	a.JDGMT_GRP_CODE,	");			// 그룹코드
				sbQuery2.append("\n	        	a.AUTO_ASGN_LIM_CNT,    ");		// 자동배정한도건수
				sbQuery2.append("\n				a.CHRG_RGNOFC_CODE,			");	// 담당지역본부
				sbQuery2.append("\n	        (SELECT COUNT(*) 	");			// 현재 심사진행건수 --> 당일배정건수로 변경
				sbQuery2.append("\n	            FROM LI_CLM_110	");
				sbQuery2.append("\n	            WHERE JDG_NO = a.JDG_NO				");
				sbQuery2.append("\n	                and ASGN_DATE = TO_CHAR(SYSDATE, 'YYYYMMDD') ");			//20111208 added by Badboy
				sbQuery2.append("\n	                and (DEL_YN <> 'Y' or DEL_YN IS NULL )	");
				sbQuery2.append("\n	        ) JDGMT_CNT						");
				sbQuery2.append("\n	    	from LI_IMN_010 a				");
				sbQuery2.append("\n	    	where 1=1						");
				sbQuery2.append("\n	        	and (DEL_YN <> 'Y' or DEL_YN is null)			");
				sbQuery2.append("\n	        	and AUTO_ASGN_OBJ_YN = 'Y'				");
				sbQuery2.append("\n	        	and (VALD_STRT_DATE is NULL or (VALD_STRT_DATE <= TO_CHAR(SYSDATE, 'YYYYMMDD')))	");
				sbQuery2.append("\n	        	and (VALD_END_DATE is NULL or (VALD_END_DATE >= TO_CHAR(SYSDATE, 'YYYYMMDD')))		");
				sbQuery2.append("\n	        	and (VOC_STRT_DATE is NULL or (VOC_STRT_DATE > TO_CHAR(SYSDATE, 'YYYYMMDD')))		");
				sbQuery2.append("\n	        	and (VOC_END_DATE is NULL or (VOC_END_DATE < TO_CHAR(SYSDATE, 'YYYYMMDD')))		");
				sbQuery2.append("\n	        	and JDGMT_AUTHY_YN = 'Y'								");
				sbQuery2.append("\n	        	and JDG_NO <> ?							");
				sbQuery2.append("\n		) X												");
				sbQuery2.append("\n		WHERE X.JDGMT_CNT < AUTO_ASGN_LIM_CNT			");
				sbQuery2.append("\n			AND X.JDGMT_GRP_CODE = ?					");
				
				if("20".equals(sJdgGroup))
					sbQuery2.append("\n			AND X.CHRG_RGNOFC_CODE = ?					");
				
				sbQuery2.append("\n	)													");
				
				pstmt = oConn.prepareStatement(sbQuery2.toString());
				pstmt.clearParameters();
				pstmt.setString(1, sNextJdgNo);
				pstmt.setString(2, sJdgGroup);
				
				if("20".equals(sJdgGroup))
					pstmt.setString(3, sRecpRgnofcCode);
				
				pstmt.executeUpdate();
				
				pstmt.close(); pstmt = null;
			
				//sQuery = " UPDATE LI_IMN_010 SET WORK_ALT_YN = 'Y' WHERE JDG_NO = ? ";
				//pstmt = oConn.prepareStatement(sQuery);
				//pstmt.clearParameters();
				//pstmt.setString(1, sNextJdgNo);
				//pstmt.executeUpdate();
			}
			else
				sNextJdgNo = "";
			
		}
		catch(Exception e) {
			COMUtil.debug("[resetNextJdgNo] Exception : " + e.toString());
			throw new Exception("[resetNextJdgNo] Exception:"+e.toString());
		}
		finally {
			if(rs != null) { rs.close(); rs=null;}
			if(pstmt != null){ pstmt.close(); pstmt=null;}
		}
		
		COMUtil.debug(">>> [resetNextJdgNo] End... <<<");
		
		return sNextJdgNo;
	}

	public static String resetNextJdgNo2(Connection oConn, String sJdgGroup, String sRecpRgnofcCode, 
			String sRecpRegnCode) throws Exception {
		
		COMUtil.debug(">>> [resetNextJdgNo2] Start... <<<");
		COMUtil.debug(">>> sRecpRgnofcCode = "+ sRecpRgnofcCode);
		COMUtil.debug(">>> sRecpRegnCode = "+ sRecpRegnCode);
		String sNextJdgNo = "";
		
		PreparedStatement pstmt = null;	
		ResultSet rs = null;
		
		StringBuffer sbQuery = new StringBuffer();
		StringBuffer sbQuery2 = new StringBuffer();
		String sQuery = "";
		
		try {
			sbQuery.append("\n	SELECT NVL(MIN(JDG_NO), '0') JDG_NO			");
			sbQuery.append("\n	FROM				");
			sbQuery.append("\n	(				");
			sbQuery.append("\n	    select a.JDG_NO,		");			// 심사자번호
			sbQuery.append("\n	        a.JDG_NM,		");				// 심사자명
			sbQuery.append("\n	        a.JDGMT_GRP_CODE,	");			// 그룹코드
			sbQuery.append("\n	        a.AUTO_ASGN_LIM_CNT,    ");		// 자동배정한도건수
			sbQuery.append("\n			a.WORK_ALT_YN,			");		// 작업할당 여부
			sbQuery.append("\n			a.CHRG_RGNOFC_CODE,			");	// 담당지역본부
			sbQuery.append("\n	        (SELECT COUNT(*) 	");			// 현재 심사진행건수 --> 당일배정건수로 변경
			sbQuery.append("\n	            FROM LI_CLM_110	");
			sbQuery.append("\n	            WHERE JDG_NO = a.JDG_NO				");
			sbQuery.append("\n	                and ASGN_DATE = TO_CHAR(SYSDATE, 'YYYYMMDD') ");			//20111208 added by Badboy
			sbQuery.append("\n	                and (DEL_YN <> 'Y' or DEL_YN IS NULL )	");
			sbQuery.append("\n	        ) JDGMT_CNT						");
			sbQuery.append("\n	    from LI_IMN_010 a  			");
			sbQuery.append("\n	    where 1=1						");
			sbQuery.append("\n	        and (DEL_YN <> 'Y' or DEL_YN is null)			");
			sbQuery.append("\n	        and AUTO_ASGN_OBJ_YN = 'Y'				");
			sbQuery.append("\n	        and (VALD_STRT_DATE is NULL or (VALD_STRT_DATE <= TO_CHAR(SYSDATE, 'YYYYMMDD')))	");
			sbQuery.append("\n	        and (VALD_END_DATE is NULL or (VALD_END_DATE >= TO_CHAR(SYSDATE, 'YYYYMMDD')))		");
			sbQuery.append("\n	        and (VOC_STRT_DATE is NULL or (VOC_STRT_DATE > TO_CHAR(SYSDATE, 'YYYYMMDD')))		");
			sbQuery.append("\n	        and (VOC_END_DATE is NULL or (VOC_END_DATE < TO_CHAR(SYSDATE, 'YYYYMMDD')))		");
			sbQuery.append("\n	        and JDGMT_AUTHY_YN = 'Y'								");
			sbQuery.append("\n	        and JDGMT_GRP_CODE = '20'								");
			sbQuery.append("\n	        and CHRG_REGN_CODE = ?									");
			sbQuery.append("\n	        and CHRG_RGNOFC_CODE <> ?								");	
			sbQuery.append("\n	) X												");
			sbQuery.append("\n	WHERE X.JDGMT_CNT < AUTO_ASGN_LIM_CNT			");
			
			pstmt = oConn.prepareStatement(sbQuery.toString());
			pstmt.clearParameters();
			pstmt.setString(1, sRecpRegnCode);
			pstmt.setString(2, sRecpRgnofcCode);
			
			rs = pstmt.executeQuery();
			
			if(rs.next())
				sNextJdgNo = rs.getString("JDG_NO");
			
			rs.close(); rs = null;
			pstmt.close(); pstmt = null;

			if(!"0".equals(sNextJdgNo)) {
				sbQuery2.append("\n	UPDATE LI_IMN_010			");
				sbQuery2.append("\n	SET	WORK_ALT_YN = 'N'		");
				sbQuery2.append("\n	WHERE JDG_NO IN ( 			");
				sbQuery2.append("\n		SELECT X.JDG_NO			");
				sbQuery2.append("\n		FROM ( 					");
				sbQuery2.append("\n	    	select a.JDG_NO,		");			// 심사자번호
				sbQuery2.append("\n	        	a.JDGMT_GRP_CODE,	");			// 그룹코드
				sbQuery2.append("\n	        	a.AUTO_ASGN_LIM_CNT,    ");		// 자동배정한도건수
				sbQuery2.append("\n				a.CHRG_RGNOFC_CODE,			");	// 담당지역본부
				sbQuery2.append("\n	        (SELECT COUNT(*) 	");			// 현재 심사진행건수 --> 당일배정건수로 변경
				sbQuery2.append("\n	            FROM LI_CLM_110	");
				sbQuery2.append("\n	            WHERE JDG_NO = a.JDG_NO				");
				sbQuery2.append("\n	                and ASGN_DATE = TO_CHAR(SYSDATE, 'YYYYMMDD') ");			//20111208 added by Badboy
				sbQuery2.append("\n	                and (DEL_YN <> 'Y' or DEL_YN IS NULL )	");
				sbQuery2.append("\n	        ) JDGMT_CNT						");
				sbQuery2.append("\n	    	from LI_IMN_010 a							");
				sbQuery2.append("\n	    	where 1=1					");
				sbQuery2.append("\n	        	and (DEL_YN <> 'Y' or DEL_YN is null)			");
				sbQuery2.append("\n	        	and AUTO_ASGN_OBJ_YN = 'Y'				");
				sbQuery2.append("\n	        	and (VALD_STRT_DATE is NULL or (VALD_STRT_DATE <= TO_CHAR(SYSDATE, 'YYYYMMDD')))	");
				sbQuery2.append("\n	        	and (VALD_END_DATE is NULL or (VALD_END_DATE >= TO_CHAR(SYSDATE, 'YYYYMMDD')))		");
				sbQuery2.append("\n	        	and (VOC_STRT_DATE is NULL or (VOC_STRT_DATE > TO_CHAR(SYSDATE, 'YYYYMMDD')))		");
				sbQuery2.append("\n	        	and (VOC_END_DATE is NULL or (VOC_END_DATE < TO_CHAR(SYSDATE, 'YYYYMMDD')))		");
				sbQuery2.append("\n	        	and JDGMT_AUTHY_YN = 'Y'								");
				sbQuery2.append("\n	        	and JDGMT_GRP_CODE = '20'								");
				sbQuery2.append("\n	        	and CHRG_REGN_CODE = ?									");
				sbQuery2.append("\n	        	and CHRG_RGNOFC_CODE <> ?								");
				sbQuery2.append("\n	        	and JDG_NO <> ?								");				
				sbQuery2.append("\n		) X												");
				sbQuery2.append("\n		WHERE X.JDGMT_CNT < AUTO_ASGN_LIM_CNT			");
				sbQuery2.append("\n	)													");
				
				pstmt = oConn.prepareStatement(sbQuery2.toString());
				pstmt.clearParameters();
				pstmt.setString(1, sRecpRegnCode);
				pstmt.setString(2, sRecpRgnofcCode);
				pstmt.setString(3, sNextJdgNo);
				
				pstmt.executeUpdate();
				
				pstmt.close(); pstmt = null;
			
				//sQuery = " UPDATE LI_IMN_010 SET WORK_ALT_YN = 'Y' WHERE JDG_NO = ? ";
				//pstmt = oConn.prepareStatement(sQuery);
				//pstmt.clearParameters();
				//pstmt.setString(1, sNextJdgNo);
				//pstmt.executeUpdate();
			}
			else
				sNextJdgNo = "";
			
		}
		catch(Exception e) {
			COMUtil.debug("[resetNextJdgNo2] Exception : " + e.toString());
			throw new Exception("[resetNextJdgNo2] Exception:"+e.toString());
		}
		finally {
			if(rs != null) { rs.close(); rs=null;}
			if(pstmt != null){ pstmt.close(); pstmt=null;}
		}
		
		COMUtil.debug(">>> [resetNextJdgNo2] End... <<<");
		
		return sNextJdgNo;
	}

}
